package com.hdc.walnut.common.utils.CRUD;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class DataSave {

    @Autowired
    private MysqlUtils mysqlUtils;

    public Map<String,Object> formSave(String tabName, ArrayList<Map<String,Object>> dataSet){
        Map<String,Object> param = new HashMap<String, Object>();
        Connection connection = mysqlUtils.getConnection();
        Statement stm = null;
        try{
            stm = connection.createStatement();
            String sqlTab ="INSERT INTO "+tabName+"(";
            String sqlVal ="VALUES(";
            int len = dataSet.size();
            for(int i =0;i<len;i++){
                sqlTab += (dataSet.get(i).get("name").toString()+",");
                sqlVal += ("'"+dataSet.get(i).get("value").toString()+"'"+",");
            }
            sqlTab=sqlTab.substring(0,sqlTab.length()-1)+")";
            sqlVal=sqlVal.substring(0,sqlVal.length()-1)+")";
            String sqlStr = sqlTab+sqlVal;
            System.out.println("[SQL]:"+sqlStr);
            int rows = stm.executeUpdate(sqlStr);
            if(rows>0){
                param.put("code","200");
                param.put("msg","success");
            }else{
                param.put("code","300");
                param.put("msg","insert 0 row");
            }
        }catch(SQLException e){
            param.put("code","300");
            param.put("msg",e.getMessage());
        }finally {
            mysqlUtils.release(connection,stm);
        }
        return  param;
    }

    public Map<String,Object> tableSave(String tabName, ArrayList<Map<String,Object>> dataSet){
        Map<String,Object> param = new HashMap<String, Object>();
        Connection connection = mysqlUtils.getConnection();
        Statement stm = null;
        try{
            List<String> listIntersection = new ArrayList<String>();
            stm = connection.createStatement();//
            String sqlStrCol="SELECT DISTINCT COLUMN_NAME FROM information_schema.columns WHERE table_name='"+tabName+"'";
            ResultSet rsc = stm.executeQuery(sqlStrCol);
            Map<String,Object> tempMapCol =dataSet.get(0);
            while(rsc.next()){
                if(tempMapCol.containsKey(rsc.getString("COLUMN_NAME"))){
                    listIntersection.add((rsc.getString("COLUMN_NAME")));
                }
            }
            String sqlTab ="INSERT INTO "+tabName+"(";
            for(String key :listIntersection) {
                sqlTab += (key + ",");
            }
            sqlTab=sqlTab.substring(0,sqlTab.length()-1)+")" + "VALUES";
            for(int i =0;i<dataSet.size();i++){
                Map<String,Object> tempMapVal = dataSet.get(i);
                String tempSqlVal ="(";
                for(String key :listIntersection){
                    tempSqlVal += ("'"+String.valueOf(tempMapVal.get(key))+"'"+",");
                }
                tempSqlVal=tempSqlVal.substring(0,tempSqlVal.length()-1)+")"+",";
                sqlTab +=tempSqlVal;
            }
            sqlTab = sqlTab.substring(0,sqlTab.length()-1);
            System.out.println("[SQL]:"+sqlTab);
            int rows = stm.executeUpdate(sqlTab);
            if(rows>0){
                param.put("code","200");
                param.put("msg","success");
            }else{
                param.put("code","300");
                param.put("msg","insert  0 row");
            }
        }catch(SQLException e){
            param.put("code","300");
            param.put("msg",e.getMessage());
        }finally {
            mysqlUtils.release(connection,stm);
        }
        return  param;
    }
}
